package com.jadcon.bedbreakfast.domain;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.stereotype.Component;

@Component("PhoneRepository")
public class PhoneRepositoryImpl implements PhoneRepository {

	private SimpleJdbcTemplate simpleJdbcTemplate;

	@SuppressWarnings("unchecked")
	public List<PhoneNumber> getAllPhoneNumbersByCustomerID(int id) {
		String sql = "select p.id, p.area_code, p.exchange, p.line_number, p.extension, p.phone_type_id, pt.description from phone p, phone_type pt where p.phone_type_id=pt.id and customer_id=?";
		return this.simpleJdbcTemplate.query(sql, new PhoneNumberMapper(), id);
	}

	public void save(List<PhoneNumber> phoneList, int customerId) {
		for (PhoneNumber phoneNumber : phoneList) {
			save(phoneNumber, customerId);
		}
	}

	public void save(PhoneNumber phoneNumber, int customerId) {
		if (phoneNumber.getId() == 0) {
			insert(phoneNumber, customerId);
		} else {
			update(phoneNumber, customerId);
		}
	}

	private void insert(PhoneNumber phoneNumber, int customerId) {

		String sql = "insert into phone (customer_id, phone_type_id, area_code, exchange, line_number, extension) values (?, ?, ?, ?, ?, ?)";
		simpleJdbcTemplate.update(sql, customerId, phoneNumber.getPhoneType()
				.getId(), phoneNumber.getAreaCode(), phoneNumber.getExchange(),
				phoneNumber.getLineNumber(), phoneNumber.getExtension());
	}

	private void update(PhoneNumber phoneNumber, int customerId) {
		String sql = "update phone SET phone_type_id=?, area_code=?, exchange=?, line_number=?, extension=? where customer_id=? and id=?";
		simpleJdbcTemplate.update(sql, phoneNumber.getPhoneType()
				.getId(), phoneNumber.getAreaCode(), phoneNumber.getExchange(),
				phoneNumber.getLineNumber(), phoneNumber.getExtension(), customerId, phoneNumber.getId());

	}

	@SuppressWarnings("unchecked")
	private static final class PhoneNumberMapper implements RowMapper {

		public PhoneNumber mapRow(ResultSet rs, int rowNum) throws SQLException {
			int id = rs.getInt("p.id");
			int areaCode = rs.getInt("p.area_code");
			int exchange = rs.getInt("p.exchange");
			int lineNumber = rs.getInt("p.line_number");
			int extension = rs.getInt("p.extension");
			int pt = rs.getInt("p.phone_type_id");

			PhoneNumber phoneNumber = new PhoneNumber(id, areaCode, exchange,
					lineNumber, extension);
			phoneNumber.setPhoneType(PhoneType.valueOf(pt));
			return phoneNumber;

		}

	}

	@Autowired
	public void setDataSource(DataSource dataSource) {
		simpleJdbcTemplate = new SimpleJdbcTemplate(dataSource);
	}

}
